-- STANDARD SCRIPT
-- INSERT DATA IN recon_t
--
--


------------------------------------------------
-- FUNCTION TO INSERT NEW ROWS INTO ACCOUNT TABLE


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION load_recon_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
  v_data_flag varchar(1);


BEGIN


-- GETTING PARAMETERS
SELECT INTO v_batch_run_date,v_cust_id,v_data_flag our_run_date,cust_id,data_flag from temp_batch_recon_t limit 1;

IF (v_data_flag = 'U') THEN

      SELECT INTO v_batch_run_date validated_date FROM  temp_batch_recon_t limit 1;

ELSIF (v_data_flag = 'E') THEN

      SELECT INTO v_batch_run_date our_run_date FROM  temp_batch_recon_t limit 1;

ELSIF (v_data_flag = 'R') THEN

      SELECT INTO v_batch_run_date recon_sent_date FROM  temp_batch_recon_t limit 1;

END IF;



---------------------------------------------------------
-- UPDATE OF recon_t TABLE
-- IF A "C" RECORD IN recon_t IS ALREADY PRESENT WITH validate_status NULL or NR WE SET IT TO AC;
-- (WE DO NOT WANT TO PUSH THE OLD RECORD THROUGH VALIDATION OR RECON - THE NEW RECORD WILL BE CONSIDERED INSTEAD)


RAISE NOTICE 'START UPDATE OF validate_status WITH NULL OR NR STATUS IN recon_t TABLE';

IF v_data_flag in ('C')  THEN

  UPDATE recon_t
  SET validate_status = 'AC' 
  FROM
     batch_recon_t as a
  WHERE
    recon_t.cust_id = a.cust_id AND
    recon_t.hid = a.hid AND
    recon_t.acctnum = a.acctnum AND
    recon_t.carrier_code = a.carrier_code AND
    recon_t.data_flag = 'C' AND
    (recon_t.validate_status IS NULL OR recon_t.validate_status = 'NR') 
;

END IF;


RAISE NOTICE 'FINISH UPDATE OF validate_status WITH NULL OR NR STATUS IN recon_t TABLE';


---------------------------------------------------------
-- INSERT INTO recon_t TABLE
PERFORM ml_upd_batch_status_recon_new_f(v_cust_id,v_batch_run_date,1,4,'START INSERT IN recon_t');
RAISE NOTICE 'START INSERT INTO recon_t TABLE';


IF v_data_flag in ('C','M')  THEN

  INSERT INTO recon_t
  SELECT
   *
  FROM batch_recon_t
  ;

  GET DIAGNOSTICS v_status = ROW_COUNT;

ELSE

  v_status := 0;

END IF;


RAISE NOTICE 'DONE INSERT INTO recon_t TABLE ';



-----------------------------------------------------------
-- A) UPDATE THE multiple_row FLAG and warning_doublepay FLAG
-- B) CREATE LOGIC IF ON SAME ACCOUNT WE GET SAME PAID DATE AND SAME PAID AMOUNT WE SCRATCH ONE VALUE

IF (v_data_flag = 'C') THEN


  RAISE NOTICE 'START UPDATE OF DOUBLE PAY INDICATOR IN recon_t TABLE';

  UPDATE recon_t
  SET validate_status = (CASE WHEN recon_t.our_run_date = v_batch_run_date 
                                   AND
                                   (recon_t.amount_paid = a.amount_paid)
                                   AND
                                   (recon_t.paid_date IS NULL or a.paid_date IS NULL or recon_t.paid_date = a.paid_date) THEN NULL 
                              ELSE                                                                                            recon_t.validate_status
                              END),
      medlytix_note = (CASE WHEN recon_t.our_run_date = v_batch_run_date
                               AND
                               (recon_t.amount_paid = a.amount_paid)
                               AND
                               (recon_t.paid_date IS NULL or a.paid_date IS NULL or recon_t.paid_date = a.paid_date) THEN 'automated msg: identical payment'
                            ELSE                                                                                          recon_t.medlytix_note
                            END),
      warning_doublepay = 'Y'     
  FROM
     (SELECT cust_id,hid,acctnum,carrier_code,our_run_date,amount_paid,paid_date FROM recon_t WHERE data_flag = 'C' and amount_paid > 0) as a
  WHERE
    recon_t.amount_paid > 0 AND
    recon_t.data_flag = 'C' AND
    recon_t.cust_id = a.cust_id AND 
    recon_t.hid = a.hid AND
    recon_t.acctnum = a.acctnum AND
    (recon_t.carrier_code <> a.carrier_code OR recon_t.our_run_date <> a.our_run_date)
;

  RAISE NOTICE 'END UPDATE OF DOUBLE PAY INDICATOR IN recon_t TABLE';




  RAISE NOTICE 'START UPDATE OF MULTIPLE ROW INDICATOR IN recon_t TABLE';

  UPDATE recon_t
  SET multiple_row = 'Y' 
  FROM
     (SELECT cust_id,hid,acctnum,carrier_code FROM recon_t WHERE data_flag = 'C') as a
  WHERE
    recon_t.data_flag = 'C' AND
    recon_t.cust_id = a.cust_id AND
    recon_t.hid = a.hid AND
    recon_t.acctnum = a.acctnum AND
    (recon_t.carrier_code <> a.carrier_code)
;

  RAISE NOTICE 'END UPDATE OF MULTIPLE ROW INDICATOR IN recon_t TABLE';



  RAISE NOTICE 'START UPDATE OF M RECORDS IN recon_t TABLE WITH recon_sent_date';

--  UPDATE recon_t
--  SET recon_sent_date = a.our_run_date 
--  FROM
--     (SELECT distinct on (cust_id,first_delivery_date) cust_id,first_delivery_date,our_run_date FROM recon_t WHERE data_flag = 'C') as a
--  WHERE
--    recon_t.data_flag = 'M'                             AND
--    recon_t.cust_id = a.cust_id                         AND
--    recon_t.first_delivery_date = a.first_delivery_date AND 
--    recon_t.recon_sent_date IS NULL
--;

  RAISE NOTICE 'END UPDATE OF M RECORDS IN recon_t TABLE WITH recon_sent_date';


END IF;



--------------------------------------------------------------------------
-- UPDATING recon_t TABLE FOR U-TYPE FILES (VALIDATION) OR E-TYPE FILES (EDIT OF RECORD)

IF (v_data_flag = 'U' or v_data_flag = 'E') THEN


  RAISE NOTICE 'START UPDATE OF recon_t TABLE FOR TYPE U-FILE';

  UPDATE recon_t
  SET

   date_billed    = a.date_billed,
   date_rejected  = a.date_rejected,
   prior_ins_date = a.prior_ins_date,
   paid_date =      a.paid_date,
   amount_paid =    a.amount_paid,
   note =           a.note,
   medlytix_note =  a.medlytix_note,
   validate_status =a.validate_status,
   recon_sent_date =a.recon_sent_date,
   validated_date = a.validated_date,
   user_id =        a.user_id

  FROM
   temp_batch_recon_t a
  WHERE
   a.seqnum = recon_t.seqnum
  ;

  v_status := 0;


  RAISE NOTICE 'END UPDATE OF recon_t TABLE FOR TYPE U-FILE';


--------------------------------------------------------------------------
-- UPDATING THE recon_sent_date IN CASE OF R-TYPE FILE (RECON OUTPUT PROCESS)

ELSIF (v_data_flag = 'R') THEN


  RAISE NOTICE 'START UPDATE OF recon_t TABLE FOR TYPE R-FILE';

  UPDATE recon_t
  SET
   recon_sent_date =  a.recon_sent_date
  FROM
   temp_batch_recon_t a
  WHERE
   a.cust_id = recon_t.cust_id
   AND
   recon_t.recon_ind = 'Y'
   AND
   recon_t.recon_sent_date IS NULL
   AND
   ( recon_t.validate_status in ('VO','AO') or (recon_t.data_flag = 'M') ) 
   AND
   ( 
     (recon_t.cust_id <> 49 AND ml_ddiff_f('d',recon_t.first_delivery_date,'now') >= 10)
     OR
     (recon_t.cust_id = 49 AND ml_ddiff_f('d',recon_t.first_delivery_date,'now') >= 15) 
   )
     
  ;

  v_status := 0;

  RAISE NOTICE 'END UPDATE OF recon_t TABLE FOR TYPE R-FILE';


END IF;
 



-----------------------------------------------------------
-- UPDATING BATCH STATUS TABLE 

UPDATE batch_status_recon_t
SET
   recon_inserted       = v_status
WHERE
  (batch_status_recon_t.last_run_date = v_batch_run_date and batch_status_recon_t.cust_id = v_cust_id);


PERFORM ml_upd_batch_status_recon_new_f(v_cust_id,v_batch_run_date,1,4,'FINISH INSERT IN recon_t');



  return v_status;
END;
$$ LANGUAGE plpgsql;
